﻿using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace TestSystem.DAL.TestSystem
{
    public class QuizHistoryDataAccess : DataAccess
    {
        public static void Delete(int historyID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@HistoryID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, historyID)     
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "QuizHistory_Delete", paramsArray);
        }

        public static void Update(int historyID, int quizID, string userID,  DateTime dateTimeStart, DateTime dateTimeComplete, int scores)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@HistoryID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current,historyID),
                    new SqlParameter("@UserID", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, userID),
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID),
                    new SqlParameter("@Scores", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, scores),
                    new SqlParameter("@DateTimeStart", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, dateTimeStart),
                    new SqlParameter("@DateTimeComplete", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, dateTimeComplete)
                };
            SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "QuizHistory_Update", paramsArray);
        }

        public static int Insert(int quizID, string userID, DateTime dateTimeStart, DateTime dateTimeComplete, int scores)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@UserID", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, userID),
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID),
                    new SqlParameter("@Scores", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, scores),
                    new SqlParameter("@DateTimeStart", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, dateTimeStart),
                    new SqlParameter("@DateTimeComplete", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, dateTimeComplete)            
                };
            return Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "QuizHistory_Insert", paramsArray));
        }

        public static int Insert(SqlTransaction tran, int quizID, string userID, DateTime dateTimeStart, DateTime dateTimeComplete, int scores)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@UserID", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, userID),
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID),
                    new SqlParameter("@Scores", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, scores),
                    new SqlParameter("@DateTimeStart", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, dateTimeStart),
                    new SqlParameter("@DateTimeComplete", SqlDbType.DateTime, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, dateTimeComplete)            
                };
            return Convert.ToInt32(SqlHelper.ExecuteNonQuery(tran, CommandType.StoredProcedure, "QuizHistory_Insert", paramsArray));
        }

        public static IDataReader GetAll()
        {
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "QuizHistory_GetAll");
        }

        public static IDataReader GetByHistoryID(int historyID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@HistoryID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, historyID)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "QuizHistory_GetByHistoryID", paramsArray);
        }

        public static IDataReader GetQuizHistoryByQuizID(int quizID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@QuizID", SqlDbType.Int, 0, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, quizID)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "QuizHistory_GetByQuizID", paramsArray);
        }

        public static IDataReader GetQuizHistoryByUserID(string userID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@UserID", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, userID)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "QuizHistory_GetByUserID", paramsArray);
        }

        public static IDataReader GetPassedByUserID(string userID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@UserID", SqlDbType.Int, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, userID)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "QuizHistory_GetPassedByUserID", paramsArray);
        }

        public static IDataReader GetFailedByUserID(string userID)
        {
            SqlParameter[] paramsArray = new SqlParameter[]
                {
                    new SqlParameter("@UserID", SqlDbType.VarChar, 50, ParameterDirection.Input, true, 10, 0, null, DataRowVersion.Current, userID)
                };
            return SqlHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "QuizHistory_GetFailedByUserID", paramsArray);
        }
    }
}
